package com.offcn.Dao;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.offcn.bean.BookBean;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class BookDao {
    QueryRunner qr = new QueryRunner(new ComboPooledDataSource());
    //添加书籍
    public int addBook(BookBean s) {
        Object[] obj ={null,s.getBname(),s.getPrice(),s.getAuthor(),s.getHome(),s.getDaTime(),s.getDiscrie(),s.getKinds(),s.getFit(),s.getPhoto()};
        String sql="insert into book values(?,?,?,?,?,?,?,?,?,?)";
        try {
            return  qr.update(sql, obj);
        } catch (SQLException e) {
            e.printStackTrace();
    }
        return 0;
    }
//查询所有图书
    public List<BookBean> selectBook() {
        String sql="select * from book";
        try {
           return qr.query(sql,new BeanListHandler<BookBean>(BookBean.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
//查询图书详情
    public BookBean selectOneBook(int id) {

        String sql="select * from book where id=?";
        try {
          return  qr.query(sql,new BeanHandler<BookBean>(BookBean.class),id);
        } catch (SQLException e) {
            e.printStackTrace();
        }return null;
    }
    //修改图书详情
    //String id, Double price, String author, String home, String daTime, String discrie
    public int updateBook(BookBean book) {
        String sql="update book set bname=?,price=?,author=?,home=?,daTime=?, discrie=?,kinds=?,fit=?,photo=? where id=?";
        Object []obj={book.getBname(),book.getPrice(),book.getAuthor(),book.getHome(),book.getDaTime(),book.getDiscrie(),book.getKinds(),book.getFit(),book.getPhoto(),book.getId()};
        try {
            return qr.update(sql,obj);
        } catch (SQLException e) {
            e.printStackTrace();
        }return 0;
    }


    public int deleteBook(int id) {
        String sql ="delete from book where id=?";
        try {
           return qr.update(sql,id);
        } catch (SQLException e) {
            e.printStackTrace();
        }return 0;
    }
//记录总记录数
    public int totalCount(String bname, String kinds) {

        StringBuilder sql=new StringBuilder("select count(*) from book where 1=1");
        if (bname !=null && !"".equals(bname)){
            sql.append(" and bname like '%"+bname+"%'");
        }
        if (kinds !=null && !"".equals(kinds)&&!"0".equals(kinds)){
            sql.append(" and kinds = '"+kinds+"'");
        }

        try {
        Long num= qr.query(sql.toString(), new ScalarHandler<Long>());
        return num.intValue();
        } catch (SQLException e) {
            e.printStackTrace();
        }return 0;
    }
}
